Entering Purchase Orders Using the Import Excel function

Purchase Orders define the items or services to be provided by a single Vendor, along with the appropriate Payment Terms, delivery dates, item descriptions, quantities, Ship Via Methods, and all other obligations and conditions. For details on the different purchase order types in DEACOM, see Purchase Order Types. To make the basic process easier in DEACOM, properly formatted excel files may be imported to create Purchase Orders.

Configuration

Refer to the Entering Purchase Orders landing page for Configuration information.

Process

Creating the spreadsheet

The following file types are compatible: .XLS, .XLSX, tab delimited files (.txt & .tsv), Comma delimited files (.csv), Pipe delimited files (.psv), and Space delimited files (.ssv).

When using this feature, the following columns must be defined at a minimum: pu_quant (Quantity Ordered) and either pr_codenum or both pu_vnddesc (Vendor Description to support Expense POs) and ch_account (Expense account). A properly formatted spreadsheet is as follows. The first row of the spreadsheet or file must contain the DEACOM field names, using only lowercase letters. The ability to add a title row is not supported.

pr_codenum

pu_quant

pu_price
100008 1 $1,000.00

Using available fields

The fields available to use in the spreadsheet are as follows:

From the Purchase Order Lines table (dtpur)

  • pu_chid - Inventory GL Account ID
    • ch_account - Account Number can be used instead of pu_chid, either with or without dashes
  • pu_pjid - Project ID
  • pu_price - Price (lines will import without this field, but at a unit price of 0.00. Pricing rules are not applied.)
  • pu_quant - Quantity ordered
  • pu_discoun - Discount Percent
  • pu_purunid - Purchase Unit (can also use un_name for Units if desired)
  • pu_prunid - Pricing Unit
  • pu_user1 - User-defined field 1
  • pu_jobnum - Job Number
  • pu_ordnum - Sales Order Number (either with or without dashes. Displays on the Linked Sales Order tab on the Edit Purchase Order Line form)
    • Sales orders can be in any status, not shipped, shipped, invoiced, or paid, but must be from the same facility as on the header of the PO to which they are being imported.
  • pu_wanted - Wanted Date
  • pu_promise - Promised Date
  • pu_duedock - Due to Dock Date
  • pu_confirm - Confirmation Date
  • pu_release - Release Date
  • pu_expires - Expiration Date
  • pu_notes - Note Text
  • pu_dockmins - Minutes to hold a Dock for this Line
  • pu_vnddesc - Vendor Part Description (for GL expense lines, when there are no pu_prid or pr_codenum)
  • pu_stanmat - Standard Materials (only used if conditions are met, same as "Add" button)
  • pu_frtcost - Freight Cost (only used if conditions are met, same as "Add" button)
  • pu_noinv - Do Not Post Inventory flag (only used if conditions are met, same as "Add" button)

From the Vendor Part Numbers table (dmprod2)

  • p2_vndcode - Vendor Part Number
  • p2_id - Unique ID

Importing the file

To create the order in DEACOM, perform the following with a spreadsheet formatted using the information in the previous sections.

  1. Navigate to Purchasing > Order Entry.
  2. Select appropriate Vendor. Note that the other header fields will be automatically populated after selecting a Vendor, but can be over-ridden by the user if desired. For additional information on the fields available to the Purchase Order header and how they are used during order entry, see the Purchasing > Order Entry encyclopedia page.
  3. Click "Import Excel" on the PO header to open the file chooser.
  4. Select the desired file containing the proper Purchase Order information and click "Import".
    1. If there is an error on any line, the error will be shown to the user with the line number of the import file.
    2. Validations are done for Chart Account, Part Number, Code Number, Job Number, and Sales Order Number. If none of these are valid, user will receive a prompt and DEACOM will go to the next line.
    3. Validations are also done for Price. If a negative price is entered, a prompt will notify the user and DEACOM will proceed to the next line.
    4. Permissions are also taken into account. User must have permission to change Price, Discount, Charge To Account, Job Number, Sales Order Number, Freight Cost, and Standard Cost to edit these fields.
  5. Review the information on the PO header and lines to ensure accuracy and completeness of the import, including checking the extensions on the Purchase Order lines and the total order amount on the header. Reviewing these items can detect errors in order entry or inconsistent pricing. Click the "Save" button to save the order and generate a Purchase Order number.